package Dao;

import been.Announcements;
import been.Health;
import been.User;

import java.sql.*;
import java.time.LocalDate;
import java.util.ArrayList;
import java.util.List;

public class communityDaoImpl implements communityDao {
    @Override
    public User findUserByUsername(String username) throws SQLException {
        User user = null;
        String sql = "select * from user where user_name=?";

        try (Connection conn = getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, username);
            try (ResultSet rst = pstmt.executeQuery()) {
                if (rst.next()) {
                    user = new User(
                            rst.getInt("user_id"),
                            rst.getString("user_name"),
                            rst.getString("password"),
                            rst.getString("role"));
                }
            }
            return user;
        } catch (SQLException e) {
            e.printStackTrace();
            throw e;
        }
    }

    @Override
    public int addUser(User user) throws SQLException {
        String sql = "insert into user values(?,?,?,?)";
        int n=0;
        try (
                Connection conn = getConnection();
                PreparedStatement pstmt = conn.prepareStatement(sql);
                ){
                    pstmt.setInt(1, user.getUser_id());
                    pstmt.setString(2, user.getUser_name());
                    pstmt.setString(3, user.getPassword());
                    pstmt.setString(4, user.getRole());
                    n=pstmt.executeUpdate();
        }catch (SQLException e)
        {
            e.printStackTrace();
        }
        return n;
    }

    @Override
    public int deleteUserByUserid(int id) throws SQLException {
        String sql = "delete from user where user_id=?";
        int n=0;
        try(
                Connection conn = getConnection();
                PreparedStatement pstmt = conn.prepareStatement(sql);
                ){
            pstmt.setInt(1, id);
            n=pstmt.executeUpdate();
        }catch (SQLException e)
        {
            e.printStackTrace();
        }
        return n;
    }

    @Override
    public User findUserById(int id) throws SQLException {
        String sql = "select * from user where user_id=?";
        User user = null;
        try (Connection conn = getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setInt(1, id);
            try (ResultSet rst = pstmt.executeQuery()) {
                if (rst.next()) {
                    user = new User(
                            rst.getInt("user_id"),
                            rst.getString("user_name"),
                            rst.getString("password"),
                            rst.getString("role"));
                }
            }
            return user;
        } catch (SQLException e) {
            e.printStackTrace();
            throw e;
        }
    }

    @Override
    public int updateUser(User user) throws SQLException {
        String sql = "update user SET user_name=?,password=?,role=? WHERE user_id=?";
        int n=0;
        try(
                Connection conn = getConnection();
                PreparedStatement pstmt = conn.prepareStatement(sql)
                ){
            pstmt.setString(1, user.getUser_name());
            pstmt.setString(2, user.getPassword());
            pstmt.setString(3, user.getRole());
            pstmt.setInt(4, user.getUser_id());
            n=pstmt.executeUpdate();
        }catch (SQLException e){
            e.printStackTrace();
        }
        return n;
    }

    @Override
    public int addHealth(Health health) {
        String sql = "insert into health values(?,?,?,?,?,?,?)";
        LocalDate now = LocalDate.now();
        int n=0;
        try(
                Connection conn= getConnection();
                PreparedStatement pstmt=conn.prepareStatement(sql);
                ){
                pstmt.setInt(1,health.getHealth_id());
                pstmt.setString(2,health.getHealth_name());
                pstmt.setString(3, health.getGender());
                pstmt.setInt(4,health.getAge());
                pstmt.setString(5, health.getChronic_diseases());
                pstmt.setString(6,health.getAllergies());
                pstmt.setDate(7, Date.valueOf(now));
                n=pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return n;
    }

    @Override
    public int deleteHealthByHealthId(int id) throws SQLException {
        String sql = "delete from health where health_id=?";
        int n=0;
        try(
                Connection conn= getConnection();
                PreparedStatement pstmt=conn.prepareStatement(sql);
                ){
            pstmt.setInt(1,id);
            n=pstmt.executeUpdate();
        }catch (SQLException e) {
            e.printStackTrace();
        }
        return n;
    }

    @Override
    public Health findHealthByHealthId(int id) throws SQLException {
        String sql = "select * from health where health_id=?";
        Health health = null;
        try(
                Connection conn= getConnection();
                PreparedStatement pstmt=conn.prepareStatement(sql);
                ){
            pstmt.setInt(1,id);
            try (ResultSet rst = pstmt.executeQuery()) {
                if (rst.next()) {
                    health = new Health(rst.getInt("health_id"),
                            rst.getString("health_name"),
                            rst.getString("gender"),
                            rst.getInt("age"),
                            rst.getString("chronic_diseases"),
                            rst.getString("allergies"),
                            rst.getDate("last_updated"));
                }
            }
        }
        catch (SQLException e) {
            e.printStackTrace();
        }
        return health;
    }

    @Override
    public List<Health> findAllHealthByHealthId() throws SQLException {
        String sql = "select * from health";
        List<Health> healths = new ArrayList<>();
        try(
                Connection conn= getConnection();
                PreparedStatement pstmt=conn.prepareStatement(sql);
                ResultSet rst=pstmt.executeQuery()
                ){
            while (rst.next()) {
                Health health = new Health(
                        rst.getInt("health_id"),
                        rst.getString("health_name"),
                        rst.getString("gender"),
                        rst.getInt("age"),
                        rst.getString("chronic_diseases"),
                        rst.getString("allergies"),
                        rst.getDate("last_updated")
                );
                healths.add(health);
            }
        }catch (SQLException e) {
            e.printStackTrace();
        }
        return healths;
    }

    public int updateHealth(Health health) throws SQLException {
        String sql="update health SET health_name=?,gender=?,age=?,chronic_diseases=?,allergies=?,last_updated=? WHERE health_id=?";
        LocalDate now = LocalDate.now();
        int n=0;
        try(
                Connection conn= getConnection();
                PreparedStatement pstmt=conn.prepareStatement(sql);
                ){
            pstmt.setString(1,health.getHealth_name());
            pstmt.setString(2,health.getGender());
            pstmt.setInt(3,health.getAge());
            pstmt.setString(4,health.getChronic_diseases());
            pstmt.setString(5,health.getAllergies());
            pstmt.setDate(6,Date.valueOf(now));
            pstmt.setInt(7,health.getHealth_id());
            n=pstmt.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return n;
    }

    @Override
    public int addAnnouncement(Announcements announcements) throws SQLException {
        String sql = "insert into announcements values(?,?,?)";
        int n=0;
        try(
                Connection conn= getConnection();
                PreparedStatement pstmt=conn.prepareStatement(sql);
                ){
            pstmt.setInt(1,announcements.getAnnouncement_id());
            pstmt.setString(2,announcements.getTitle());
            pstmt.setString(3,announcements.getContent());
            n=pstmt.executeUpdate();
        }catch (SQLException e) {
            e.printStackTrace();
        }
        return n;
    }

    @Override
    public List<Announcements> findAllAnnouncements() throws SQLException {
        ArrayList<Announcements> announcementsList = new ArrayList<>();
        String sql = "select * from announcements";
        try(
                Connection conn= getConnection();
                PreparedStatement pstmt=conn.prepareStatement(sql);
                ResultSet rst=pstmt.executeQuery()
                ){
            while (rst.next()) {
                announcementsList.add(new Announcements(
                        rst.getInt("announcement_id"),
                        rst.getString("title"),
                        rst.getString("content")
                ));
            }
        }catch (SQLException e) {
            e.printStackTrace();
        }
        return announcementsList;
    }

    @Override
    public int deleteAnnouncementByAnnouncementId(int id) throws SQLException {
        String sql = "delete from announcements where announcement_id=?";
        int n=0;
        try(
                Connection conn= getConnection();
                PreparedStatement pstmt=conn.prepareStatement(sql);
                ){
            pstmt.setInt(1,id);
            n=pstmt.executeUpdate();
        }catch (SQLException e) {
            e.printStackTrace();
        }
        return n;
    }
    public Announcements findAnnouncementByAnnouncementId(int id) throws SQLException {
        String sql = "select * from announcements where announcement_id=?";
        Announcements announcements = null;
        try(
                Connection conn= getConnection();
                PreparedStatement pstmt=conn.prepareStatement(sql);
                ){
            pstmt.setInt(1,id);
            try (ResultSet rst=pstmt.executeQuery()) {
                if (rst.next()) {
                    announcements = new Announcements(rst.getInt("announcement_id"),
                            rst.getString("title"),
                            rst.getString("content"));
                }
            }
        }catch (SQLException e) {
            e.printStackTrace();
        }
        return announcements;
    }

    public int updateAnnouncement(Announcements announcements) throws SQLException {
        String sql="update announcements set title=?,content=? where announcement_id=?";
        int n=0;
        try(
                Connection conn= getConnection();
                PreparedStatement pstmt=conn.prepareStatement(sql);
                ){
            pstmt.setString(1,announcements.getTitle());
            pstmt.setString(2,announcements.getContent());
            pstmt.setInt(3,announcements.getAnnouncement_id());
            n=pstmt.executeUpdate();
        }catch (SQLException e) {
            e.printStackTrace();
        }
        return n;
    }
}
